package edu.byu.intex2.data.dao;

import edu.byu.intex2.data.Cache;
import edu.byu.intex2.data.ConnectionPool;
import edu.byu.intex2.data.DataException;
import edu.byu.intex2.data.bo.Sale;
import edu.byu.intex2.data.bo.SaleLine;
import edu.byu.intex2.data.bo.TradeInLine;
import java.sql.*;
import java.util.LinkedList;
import java.util.List;

/**
 * Sale Data Access Object
 * @author Brady
 */
public class SaleDAO {

    /////////////////////////////////////////////
    ///   Singleton code
    private static SaleDAO instance = null;

    /** Creates a new instance of SaleDAO */
    private SaleDAO() {
    }//constructor

    /** Retrieves the single instance of this class */
    public static synchronized SaleDAO getInstance() {
        if (instance == null) {
            instance = new SaleDAO();
        }
        return instance;
    }//getInstance

    ////////////////////////////////////////////
    ///   CREATE methods
    /**
     * Creates a new sale in the database */
    public Sale create(String id) throws DataException {
        Sale sale = new Sale(id);
        sale.setObjectAlreadyInDB(false);
        Cache.getInstance().put(sale.getId(), sale);
        return sale;
    }//create


    ////////////////////////////////////////////
    ///   READ methods
    /**
     * Reads an existing sale from the database     */
    public Sale read(String id) throws DataException {
        // Check Cache
        if (Cache.getInstance().containsKey(id)) {
            return (Sale) Cache.getInstance().get(id);
        }

        // Get Connection
        Connection conn = ConnectionPool.getInstance().get();

        // Try Reading with connection
        try {
            return read(id, conn);
        } catch (Exception e) {
            throw new DataException("An error occurred while reading the business object information.", e);
        } finally {
            ConnectionPool.getInstance().release(conn);
        }
    }

    /**
     * Internal method to read an existing sale from the database
     * @return sale
     */
    synchronized Sale read(String id, Connection conn) throws Exception {
        // Check Cache
        if (Cache.getInstance().containsKey(id)) {
            return (Sale) Cache.getInstance().get(id);
        }

        // Prepare a SQL Statement
        PreparedStatement stmt = conn.prepareStatement("SELECT * FROM sale where id=?");

        try {
            stmt.setString(1, id);
            ResultSet rs = stmt.executeQuery(); // execute query
            if (rs.next()) {
                // Read the record with the result set
                return readRecord(rs, conn);
            }//if
            throw new DataException("Sale with id '" + id + "' not found.");
        } finally {
            stmt.close();
        }
    }//read

    /** Internal method to create a sale object from a record  */
    synchronized Sale readRecord(ResultSet rs, Connection conn) throws Exception {
        if (Cache.getInstance().containsKey(rs.getString("id"))) {
            return (Sale) Cache.getInstance().get(rs.getString("id"));
        }
        // Create new sale object
        Sale sale = new Sale(rs.getString("id"));

        // Sale is already in DB
        sale.setObjectAlreadyInDB(true);

        // Put sale in cache
        Cache.getInstance().put(sale.getId(), sale);

        // Set Sale Attributes
        sale.setTotalPrice(rs.getDouble("total_price"));
        sale.setTax(rs.getDouble("tax"));
        sale.setFinanced(rs.getString("financed"));
        sale.setDownPayment(rs.getDouble("down_payment"));
        sale.setPaymentPlan(rs.getString("payment_plan"));
        sale.setDiscount(rs.getDouble("discount"));
        sale.setDate(rs.getDate("sale_date"));
        sale.setSalespersonBonus(rs.getDouble("salesperson_bonus"));
        sale.setSalespersonId(rs.getString("salesperson_id"));

        // Sale is dirty and needs to be saved to DB
        sale.setDirty(false);

        return sale;
    }//readRecord


    /////////////////////////////////////////////
    ///   UPDATE methods
    /** Saves an existing sale in the database */
    public void save(Sale sale) throws DataException {
        // get a connection from the pool, save all of the sales first using the other sale method and then commit them in the database
        Connection conn = ConnectionPool.getInstance().get();

        try {
            save(sale, conn); // save with connection
            conn.commit();  // commit the db transaction
        } catch (Exception e) {
            try {
                //rollback if there were any errors saving, catch any other errors you might have
                conn.rollback();
            } catch (SQLException e2) {
                throw new DataException("Could not roll back the sale database transaction!", e2);
            }
            throw new DataException("An error occurred while saving the sale object information.", e);
        } finally {
            // release the connection
            ConnectionPool.getInstance().release(conn);
        }
    }

    /** Internal method to update a sale in the database */
    void save(Sale sale, Connection conn) throws Exception {
        // put the sale object to cache
        Cache.getInstance().put(sale.getId(), sale);

        // decide whether to update or insert into the database
        if (sale.isObjectAlreadyInDB() && sale.isDirty()) {
            update(sale, conn);
        } else if (sale.isDirty()) {
            insert(sale, conn);
        }//if

        // Save sale lines
        List<SaleLine> saleLines = sale.getSalesLines();
        for (SaleLine sl : saleLines){
            SaleLineDAO.getInstance().saveNoCommit(sl, conn);
        }

        // Save trade in lines
        List<TradeInLine> tradeInLines = sale.getTradeInLines();
        for (TradeInLine tl : tradeInLines){
            TradeInLineDAO.getInstance().saveNoCommit(tl, conn);
        }
    }

    /** External method to update or create a sale in the database without committing connection */
    public void saveNoCommit(Sale sale, Connection conn, Boolean alreadyInDB) throws Exception {
        // decide whether to update or insert into the database
        if (alreadyInDB) {
            update(sale, conn);
        } else {
            insert(sale, conn);
        }
    }

    /** Saves an existing sale to the database */
    private void update(Sale sale, Connection conn) throws Exception {
        // use a prepared statement to prevent sql injection
        PreparedStatement stmt = conn.prepareStatement("UPDATE sale SET total_price=?, tax=?, financed=?, down_payment=?, " +
                                                         "payment_plan=?, discount=?, sale_date=?, salesperson_bonus=?, salesperson_id=?  WHERE id=?");
        try {
            // Get Sale Attributes and add them to the statement
            stmt.setDouble(1, sale.getTotalPrice());
            stmt.setDouble(2, sale.getTax());
            stmt.setString(3, sale.getFinanced());
            stmt.setDouble(4, sale.getDownPayment());
            stmt.setString(5, sale.getPaymentPlan());
            stmt.setDouble(6, sale.getDiscount());
            stmt.setDate(7, new java.sql.Date(sale.getDate().getTime()));
            stmt.setDouble(8, sale.getSalespersonBonus());
            stmt.setString(9, sale.getSalespersonId());
            stmt.setString(10, sale.getId());
            stmt.execute();

            // Sale is clean!
            sale.setDirty(false);
        } finally {
            stmt.close();  // make sure to close the prepared statement
        }
    }

    /** Inserts a new sale into the database */
    private void insert(Sale sale, Connection conn) throws Exception {
        PreparedStatement stmt = conn.prepareStatement("INSERT INTO sale (id, total_price, tax, financed, down_payment, payment_plan, discount, sale_date, salesperson_bonus, salesperson_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
        try {
            // Set Sale Attributes
            stmt.setString(1, sale.getId());
            stmt.setDouble(2, sale.getTotalPrice());
            stmt.setDouble(3, sale.getTax());
            stmt.setString(4, sale.getFinanced());
            stmt.setDouble(5, sale.getDownPayment());
            stmt.setString(6, sale.getPaymentPlan());
            stmt.setDouble(7, sale.getDiscount());
            stmt.setDate(8, new java.sql.Date(sale.getDate().getTime()));
            stmt.setDouble(9, sale.getSalespersonBonus());
            stmt.setString(10, sale.getSalespersonId());
            stmt.execute();

            sale.setObjectAlreadyInDB(true); // now in DB
            sale.setDirty(false); // object is clean
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            stmt.close(); // close the prepared statement
        }
    }

    /////////////////////////////////////////////////
    ///   DELETE methods
    /** We do not support deleting of business objects in this application */
    public void delete(Sale sale) throws DataException {
        throw new RuntimeException("Nice try. The delete function is not supported in this application.");
    }

    ////////////////////////////////////////////////
    ///   SEARCH methods

}
